﻿using EfCoreDemo.Data;
using EfCoreDemo.Domain;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography.X509Certificates;

namespace EfCoreDemoConsole
{
    class Program
    {
        #region Test
        static void Main(string[] args)
        {
            
            DbContextOptionsBuilder dbContextOptionsBuilder = new DbContextOptionsBuilder();
            dbContextOptionsBuilder.UseSqlServer("Server=.;uid=sa;pwd=shris2020;database=EfCoreDemo;");
            using var context = new DemoContext(dbContextOptionsBuilder.Options);

            //var user = new UserTest()
            //{
            //    Name = "小明",
            //    Age = 33,
            //    Resume = new UserResume() { Context = "新来的",Certification=new Certification() { CertificationName="一级证书"} }
            //};

            //context.UserTests.Add(user);

            //var user = context.UserTests.Where(x => x.Name == "王豪杰")
            //    .Include(y => y.Resume).Single();

            //var ceter = new Certification() { CertificationName = "一级证书" };
            //user.Resume.Certification = ceter;

            //context.UserTests.Attach(user);

            //var resume = new UserResume() { Context = "新来的", User = user };

            //context.UserTests.Remove(user);
            //var x = context.UserTests.ToList().Select(x=>x.Grade).Distinct(x=>x.)

            //var resume = context.UserResumes.Where(x=>x.Id==new Guid("21c33cc5-9c8a-4c6d-eff9-08d90c5ae6b0")).Single();
            //context.UserResumes.Remove(resume);


            //var user = context.UserTests.Where(x => x.Name == "王豪杰")
            //    .Include(x => x.Resume).ThenInclude(x=>x.Certification).Single();

            //user.Resume.Certification.CertificationName = "三级证书";

            //context.Certifications.Update(user.Resume.Certification);


            //user.Resume.Certification = new Certification() { CertificationName = "二级证书" };

            //context.UserResumes.Attach(user.Resume);

            //var user = new UserTest()
            //{
            //    Name = "周平",
            //    Age = 50,
            //    Grade = 3,
            //};

            //var project = new ProjectTest()
            //{
            //    ProjectName = "跨江大桥",
            //    ProjectDescription = "斜拉索桥"
            //};

            //var userProj = new UserProject()
            //{
            //    User = user,
            //    Project = project,
            //    AuthorNumber = 1
            //};

            //context.UserProjects.Add(userProj);

            //var x = context.UserTests.Where(x => x.Name == "周平").FirstOrDefault();

            //context.Remove(x);

            //var user1 = new UserTest()
            //{
            //    Name = "陈建",
            //    Age = 32,
            //    Grade = 2,
            //};

            //var project1 = new ProjectTest()
            //{
            //    ProjectName = "路由器",
            //    ProjectDescription = "造型前卫的路由器"

            //};

            //var project2 = new ProjectTest()
            //{
            //    ProjectName = ".netcore",
            //    ProjectDescription = "机加工产线框架"

            //};

            //var project3 = new ProjectTest()
            //{
            //    ProjectName = "大飞机",
            //    ProjectDescription = "打飞机外形设计"

            //};


            //context.AddRange(user, user1);



            //var proj = context.ProjectTests.Where(x => x.ProjectName == ".netcore").Select(y => y.Id).FirstOrDefault();
            //var proj = context.ProjectTests.Where(x => x.ProjectName == ".netcore").FirstOrDefault();
            //var proj2 = context.ProjectTests.Where(x => x.ProjectName == "大飞机").FirstOrDefault();

            //var user = context.UserTests.Where(x => x.Name == "张朝阳").FirstOrDefault();
            //var user2 = context.UserTests.Where(x => x.Name == "陈建").FirstOrDefault();

            //var userProj = new UserProject()
            //{
            //    User = user,
            //    Project = proj,
            //    AuthorNumber = 2
            //};

            //var userProj2 = new UserProject()
            //{
            //    User = user2,
            //    Project = proj2,
            //    AuthorNumber = 2
            //};

            //context.UserProjects.AddRange(userProj,userProj2);

            //////context.UserTests

            //var resume=new UserResume() { }


            //直接在player上修改
            //var game = context.Games.Include(x => x.GamePlayers)
            //    .ThenInclude(y => y.Player).First();

            //var firstPlayer = game.GamePlayers[0].Player;
            //firstPlayer.Name += "#";
            //context.Players.Update(firstPlayer);

            //在级联关系上更改
            //var game = context.Games.Include(x => x.GamePlayers)
            //    .ThenInclude(y => y.Player).First();

            //var firstPlayer = game.GamePlayers[0].Player;
            //firstPlayer.Name += "$";
            //{
            //    using var newcontext = new DemoContext();
            //    newcontext.Update(firstPlayer);
            //    var count = newcontext.SaveChanges();
            //    Console.WriteLine(count);
            //}

            //entry 方法
            //var game = context.Games.Include(x => x.GamePlayers)
            //    .ThenInclude(y => y.Player).First();

            //var firstPlayer = game.GamePlayers[0].Player;
            //firstPlayer.Name += "@";
            //{
            //    using var newcontext = new DemoContext();
            //    newcontext.Entry(firstPlayer).State = EntityState.Modified;
            //    var cut = newcontext.SaveChanges();
            //    Console.WriteLine(cut);
            //}


            //添加级联数据
            //var game = context.Games.First();
            //game.GamePlayers.Add(new GamePlayer() { PlayerId=4});


            //删除级联关系
            //var game = context.Games.Include(x => x.GamePlayers).First();
            //var gameplayer = game.GamePlayers.Where(x => x.PlayerId == 4).First();
            //game.GamePlayers.Remove(gameplayer);



            //var user = context.UserTests.Where(x => x.Name == "小明")
            //    .Include(x => x.Resume)
            //        .ThenInclude(x => x.Certification).FirstOrDefault();

            //user.Resume = new UserResume() { Context = "新来的逗逼" };

            var playerClubes = context.PlayerClubs.FromSqlRaw("select * from PlayserClubView").ToList();

            foreach (var pc in playerClubes)
            {
                Console.WriteLine($"{pc.PlayerId}  {pc.PlayerName}  {pc.ClubName}");
            }

            //var userName = context.UserTests.FromSqlRaw("select Name from UserTests").ToList();

            //var count = context.Database.ExecuteSqlRaw("exec [dbo].[RemoveGamePlayerProcedure] {0}", 2);

            //Console.WriteLine(count);
            //foreach(var pc in playerClubes)
            //{
            //    Console.WriteLine($"{pc.PlayerId}  {pc.PlayerName}  {pc.ClubName}");
            //}

            //var count = context.SaveChanges();
            //Console.WriteLine(count);


            //var a = context.UserTests.Select(i => i.Projects).ToList();
            //var b = context.UserTests.SelectMany(i => i.Projects).ToList();
            //Console.WriteLine("end");
        }

        #endregion


        #region 插入
        //static void Main(string[] args)
        //{
        //    using var context = new DemoContext();


        //    //添加一笔数据
        //    //var serieA = new League
        //    //{ 
        //    //    Country="Italy",
        //    //    Name="Serie A"
        //    //};
        //    //context.Leagues.Add(serieA);

        //    //添加多笔数据
        //    //var serieA1 = new League
        //    //{
        //    //    Country = "Italy",
        //    //    Name = "Serie A1"
        //    //};
        //    //var serieA2 = new League
        //    //{
        //    //    Country = "Italy",
        //    //    Name = "Serie A2"
        //    //};
        //    //context.Leagues.AddRange(serieA1, serieA2);
        //    //context.Leagues.AddRange(new List<League> { serieA1,serieA2});


        //    //添加不同类型数据
        //    var serieA = context.Leagues.Single(x => x.Name=="Serie A");

        //    var serieB = new League
        //    {
        //        Country = "Italy",
        //        Name = "Serie B"
        //    };
        //    var serieC = new League
        //    {
        //        Country = "Italy",
        //        Name = "Serie C"
        //    };
        //    var milan = new Club
        //    {
        //        Name = "AC Milan",
        //        City = "Milan",
        //        DateOfEstablishment = new DateTime(1899, 12, 16),
        //        League = serieA
        //    };
        //    context.AddRange(serieB, serieC, milan);

        //    var count = context.SaveChanges();

        //    Console.WriteLine(count);
        //}
        #endregion

        #region 查询
        //static void Main(string[] args)
        //{
        //    using var context = new DemoContext();

        //    var country = "Italy";

        //    //这种使用参数传入（country） logger 输出是不会显示参数的，需要在logger中额外配置 EnableSensitiveDataLogging()
        //    var leagues = context.Leagues
        //        .Where(x => x.Country == country)
        //        .ToList();

        //    //模糊查询-1
        //    var leaguesLike1 = context.Leagues
        //        .Where(x => x.Country.Contains("e"))
        //        .ToList();

        //    //模糊查询-2
        //    var leaguesLike2 = context.Leagues
        //        .Where(x => EF.Functions.Like(x.Country,"%e%"))
        //        .ToList();

        //    //这种情况，会在foreach中打开数据库连接，直到执行完毕后再关闭，当循环内执行时间过长时，会影响数据库效率
        //    //foreach(var league in context.Leagues)
        //    //{
        //    //    //...
        //    //}

        //    //所以一般情况会使用数据库查询结构foreach
        //    foreach (var league in leagues)
        //    {
        //        Console.WriteLine(league.Name);
        //    }


        //    //ToList(), First(), FirsOrDefault()
        //    //Single(), SingleOrDefault(), Last(), LastOrDefault()
        //    //Count(), LongCount(), Min(), Max(), Average(), Sum()
        //    //Find()
        //    var first = context.Leagues.FirstOrDefault(x => EF.Functions.Like(x.Country, "%e%"));

        //    Console.WriteLine(first?.Name);

        //    //需要排序，不加 orderby(orderbydescending) 会抛 exception
        //    var last = context.Leagues.OrderByDescending(x=>x.Id).LastOrDefault(x => EF.Functions.Like(x.Country, "%e%"));
        //    Console.WriteLine(last?.Name);
        //}
        #endregion

        #region 删除
        //static void Main(string[] args)
        //{
        //    using var context = new DemoContext();

        //    //ef 只能删除被追踪的数据,所以必须先查出来
        //    var milan = context.Clubs.Single(x => x.Name.Contains("AC"));


        //    //调用删除方法
        //    context.Clubs.Remove(milan);
        //    //context.Remove(milan);

        //    //context.Clubs.RemoveRange(milan, milan);
        //    //context.RemoveRange(milan, milan);

        //    var count = context.SaveChanges();
        //    Console.WriteLine(count);
        //}
        #endregion

        #region 修改
        //static void Main(string[] args)
        //{
        //    using var context = new DemoContext();

        //    //首先数据必须被context 追踪才能修改
        //    //var leauue = context.Leagues.First();

        //    //leauue.Name += "~~";

        //    //var count = context.SaveChanges();

        //    //Console.WriteLine(count);

        //    //var leagues = context.Leagues.Skip(1).Take(3).ToList();

        //    //foreach(var league in leagues)
        //    //{
        //    //    league.Name += "~~";
        //    //}
        //    //var count = context.SaveChanges();

        //    //Console.WriteLine(count);


        //    //大多数数据都是从前端传进来的，没法从数据库查询
        //    var leagues = context.Leagues.AsNoTracking().Skip(1).Take(3).ToList();  //这里用 asnotracking 模拟 前端过来数据

        //    foreach(var league in leagues)
        //    {
        //        league.Name += "++";
        //    }

        //    context.Leagues.UpdateRange(leagues);
        //    var count = context.SaveChanges();

        //    Console.WriteLine(count);
        //}
        #endregion

        #region 添加关联数据
        //static void Main(string[] args)
        //{
        //    using var context = new DemoContext();

        //    //级联添加
        //    //var serieA = context.Leagues.SingleOrDefault(x => x.Name.Contains("Serie A"));

        //    //var juventus = new Club
        //    //{
        //    //    League = serieA,
        //    //    Name = "Juventus",
        //    //    City = "Torino",
        //    //    DateOfEstablishment = new DateTime(1897, 11, 1),
        //    //    Players=new List<Player>
        //    //    {
        //    //        new Player
        //    //        { 
        //    //            Name="C.Ronaldo",
        //    //            DateOfBirthday=new DateTime(1985,2,5)
        //    //        }
        //    //    }
        //    //};
        //    //context.Clubs.Add(juventus);
        //    //var count = context.SaveChanges();
        //    //Console.WriteLine(count);

        //    //var juventus = context.Clubs.Single(x => x.Name == "Juventus");
        //    //juventus.Players.Add(new Player
        //    //{
        //    //    Name="Gonzalo Higuain",
        //    //    DateOfBirthday=new DateTime(1987,12,10)
        //    //});
        //    //int count = context.SaveChanges();

        //    //Console.WriteLine(count);


        //    // 使用 add remove update 都会追踪context变化，这里只是增加了一个球员，
        //    //却会同时 update club 的记录 然后再 insert 一个player
        //    //var juevntus = context.Clubs.Single(x => x.Name == "Juventus");

        //    //juevntus.Players.Add(new Player
        //    //{
        //    //    Name="Matthijs de Ligt",
        //    //    DateOfBirthday=new DateTime(1999,12,18)
        //    //});

        //    //{
        //    //    using var newContext = new DemoContext();
        //    //    newContext.Clubs.Update(juevntus);
        //    //    var count = newContext.SaveChanges();
        //    //    Console.WriteLine(count);
        //    //}

        //    //使用 attach 实现上面需求
        //    // attach 为附加意思，发现clubs 中attach 对象是有主键的对象，所以不会修改club
        //    //但是发现club 中有一个 player 对象，没有主键，所以就执行新增操作
        //    //var juevntus = context.Clubs.Single(x => x.Name == "Juventus");

        //    //juevntus.Players.Add(new Player
        //    //{
        //    //    Name = "Miralem Pjanic",
        //    //    DateOfBirthday = new DateTime(1999, 12, 18)
        //    //});

        //    //{
        //    //    using var newContext = new DemoContext();
        //    //    newContext.Clubs.Attach(juevntus);
        //    //    var count = newContext.SaveChanges();
        //    //    Console.WriteLine(count);
        //    //}


        //    //添加一个球员简历， 但是为什么球员那里的 ResumeId 不会变？
        //    var resume = new Resume
        //    {
        //        PlayerId=1,
        //        Description="..."
        //    };

        //    context.Resumes.Add(resume);
        //    var count = context.SaveChanges();

        //    Console.WriteLine(count);
        //}
        #endregion


        #region 加载关联数据
        //static void Main(string[] args)
        //{
        //    //预加载
        //    using var context = new DemoContext();
        //    //var clubs = context.Clubs
        //    //    .Where(a=>a.Id>0)
        //    //    .Include(b => b.League)
        //    //    .Include(c=>c.Players)
        //    //        .ThenInclude(d=>d.Resume)
        //    //    .Include(e => e.Players)
        //    //        .ThenInclude(f => f.GamePlayers)
        //    //            .ThenInclude(g=>g.Game)
        //    //    .ToList();


        //    //context 无法追踪匿名类，只能追踪它识别的类，一下方法中select中的匿名类无法追踪
        //    //但是匿名类中 Players 将被追踪
        //    //var info = context.Clubs
        //    //    .Where(x => x.Id > 0)
        //    //    .Select(x => new
        //    //    {
        //    //        x.Id,
        //    //        LeagueName = x.League.Name,
        //    //        x.Name,
        //    //        Players = x.Players
        //    //            .Where(p => p.DateOfBirthday > new DateTime(1900, 1, 1))
        //    //    }).ToList();

        //    //foreach (var data in info)
        //    //{
        //    //    foreach (var player in data.Players)
        //    //    {
        //    //        player.Name += "~";
        //    //    }
        //    //}
        //    //context.SaveChanges();

        //    //查询一笔数据，这种方法无法应用与一笔集合数据的查询
        //    var clb = context.Clubs.First();
        //    //将这笔数据的集合类型的关联数据查询出来
        //    context.Entry(clb).Collection(x => x.Players).Load();

        //    //加上查询条件的加载
        //    context.Entry(clb).Collection(x => x.Players)
        //        .Query().Where(x => x.DateOfBirthday > new DateTime(1990)).Load();

        //    //将这笔数据的单个类型的关联数据查询出来
        //    context.Entry(clb).Reference(x => x.League).Load();


        //    //GamePlayer 表由于不在 DemoContext 的 DbSet 属性中，为了让其被追踪，使用如下方法：
        //    var gamePlayers = context.Set<GamePlayer>().Where(x => x.PlayerId > 0).ToList();

        //}
        #endregion

        #region 修改关联数据

        //static void Main(string[] args)
        //{
        //    using var context = new DemoContext();


        //    //直接在player上修改
        //    //var game = context.Games.Include(x => x.GamePlayers)
        //    //    .ThenInclude(y => y.Player).First();

        //    //var firstPlayer = game.GamePlayers[0].Player;
        //    //firstPlayer.Name += "#";
        //    //context.Players.Update(firstPlayer);

        //    //在级联关系上更改
        //    //var game = context.Games.Include(x => x.GamePlayers)
        //    //    .ThenInclude(y => y.Player).First();

        //    //var firstPlayer = game.GamePlayers[0].Player;
        //    //firstPlayer.Name += "$";
        //    //{
        //    //    using var newcontext = new DemoContext();
        //    //    newcontext.Update(firstPlayer);
        //    //    var count = newcontext.SaveChanges();
        //    //    Console.WriteLine(count);
        //    //}

        //    //entry 方法
        //    //var game = context.Games.Include(x => x.GamePlayers)
        //    //    .ThenInclude(y => y.Player).First();

        //    //var firstPlayer = game.GamePlayers[0].Player;
        //    //firstPlayer.Name += "@";
        //    //{
        //    //    using var newcontext = new DemoContext();
        //    //    newcontext.Entry(firstPlayer).State = EntityState.Modified;
        //    //    var cut = newcontext.SaveChanges();
        //    //    Console.WriteLine(cut);
        //    //}


        //    //添加级联数据
        //    //var game = context.Games.First();
        //    //game.GamePlayers.Add(new GamePlayer() { PlayerId=4});


        //    //删除级联关系
        //    //var game = context.Games.Include(x => x.GamePlayers).First();
        //    //var gameplayer = game.GamePlayers.Where(x => x.PlayerId == 4).First();
        //    //game.GamePlayers.Remove(gameplayer);



        //    var user = context.UserTests.Where(x => x.Name == "小明")
        //        .Include(x => x.Resume)
        //            .ThenInclude(x => x.Certification).FirstOrDefault();

        //    user.Resume = new UserResume() { Context = "新来的逗逼" };

        //    var count = context.SaveChanges();
        //    Console.WriteLine(count);
        //}

        #endregion
    }

}
